package devbureau.fstore.common.db;

import devbureau.fstore.common.Customer;
import devbureau.fstore.common.CustomerData;
import devbureau.fstore.common.CustomerDataImpl;
import devbureau.fstore.common.CustomerRegistry;
import devbureau.fstore.util.DBHelper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

public class CustomerRegistryDBImpl implements CustomerRegistry{

    private static final String SQL_CODE_DUPLICATE_KEY = "-803";
    private static final String SQL_STATE_DUPLICATE_KEY = "23505";
    private static Logger logger = Logger.getLogger(CustomerRegistryDBImpl.class);

    private static final String SQL_SELECT_ALL_CUSTOMERS = "select ID, LABEL, INN, ADDRESS, IS_ACTIVE from STORE.CUSTOMER order by LABEL, ADDRESS";
    private static final String SQL_SELECT_ACTIVE_CUSTOMERS = "select ID, LABEL, INN, ADDRESS, IS_ACTIVE from STORE.CUSTOMER where IS_ACTIVE = 1 order by LABEL, ADDRESS";
    private static final String SQL_INSERT_CUSTOMER = "insert into STORE.CUSTOMER(ID,LABEL,INN,ADDRESS) values (?,?,?,?)";
	private static final String SQL_NEXT_CUSTOMER_ID = "values nextval for STORE.CUSTOMER_SEQ";
    private static final String SQL_UPDATE_CUSTOMER = "update STORE.CUSTOMER set LABEL = ?, INN = ? , ADDRESS = ? where ID = ?";
    private static final String SQL_SELECT_CUSTOMER = "select LABEL, INN, ADDRESS,IS_ACTIVE from STORE.CUSTOMER where ID = ?";
    private static final String SQL_ACTIVATE_CUSTOMER = "update STORE.CUSTOMER set IS_ACTIVE = ? where ID = ?";

    public Integer registerCustomer(Customer customer) throws Exception{
        
        logger.debug("#registerCustomer enter");
        Connection connection = null;
        Integer customerId = null;
        PreparedStatement psNextId =null;
        ResultSet rsNextId  = null;
        PreparedStatement ps =null;
        
        try{
            connection = DBHelper.getConnection();
            psNextId = connection.prepareStatement(SQL_NEXT_CUSTOMER_ID);
            rsNextId  = psNextId.executeQuery();
            if(rsNextId.next()){
                customerId = rsNextId.getInt(1);
            }
            rsNextId.close();
            psNextId.close();

            ps = connection.prepareStatement(SQL_INSERT_CUSTOMER);
            ps.setInt(1, customerId);
            ps.setString(2, customer.getLabel());
            ps.setString(3, customer.getINN());
            ps.setString(4, customer.getAddress());

            ps.executeUpdate();

            ps.close();
            
        }catch(Exception ex){
            logger.fatal(ex);
            String message = "Создать грузополучателя не удалось. Ошибка: ";
            if (ex.getMessage().contains(SQL_CODE_DUPLICATE_KEY) && ex.getMessage().contains(SQL_STATE_DUPLICATE_KEY)){
                message+= "грузополучатель с названием " + customer.getLabel() + " уже существует.";
            }else{
                message += ex.getMessage();
            }
            throw new Exception( message);
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#registerCustomer customerId = " + customerId);
        logger.debug("#registerCustomer exit");
        return customerId;
    }

    public void updateCustomer(Integer id,Customer customer) throws Exception{
        logger.debug("#registerCustomer enter");
        Connection connection = null;
        PreparedStatement ps =null;
        
        try{
            connection = DBHelper.getConnection();
            ps = connection.prepareStatement(SQL_UPDATE_CUSTOMER);
            
            ps.setString(1, customer.getLabel());
            ps.setString(2, customer.getINN());
            ps.setString(3, customer.getAddress());
            ps.setInt(4, id);
            ps.executeUpdate();
            ps.close();
            
        }catch(Exception ex){
            String message = "Изменить грузополучателя не удалось. Ошибка: ";
            if (ex.getMessage().contains(SQL_CODE_DUPLICATE_KEY) && ex.getMessage().contains(SQL_STATE_DUPLICATE_KEY)){
                message+= "грузополучатель с названием " + customer.getLabel() + " уже существует.";
            }else{
                logger.fatal(ex);
               message += ex.getMessage();
            }
            throw new Exception( message);
        }finally{
            DBHelper.closeConnection(connection);
        }
    }

    public List<CustomerData> getCustomers(String sql) throws Exception {
        logger.debug("#getAllCustomers enter");
        List<CustomerData> customers = new ArrayList<CustomerData>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try{
            connection = DBHelper.getConnection();
            preparedStatement= connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while(rs.next()){
                Integer id = rs.getInt("ID");
                String label = rs.getString("LABEL");
                String inn = rs.getString("INN");
                String address = rs.getString("ADDRESS");
                Boolean active = new Boolean(rs.getInt("IS_ACTIVE")==1);
                CustomerData customer = new CustomerDataImpl(id,label, inn, address,active);
                customers.add(customer);
            }

        }catch(Exception e){
            logger.fatal(e);
            throw new Exception("Получить данные о грузополучателях не удалось. Ошибка : " + e.getMessage(),e);
        }finally{
            DBHelper.closeConnection(connection);
        }
        return customers;
    }

    public CustomerData getCustomerById(Integer customerId) throws Exception {
        logger.debug("#getCustomerById enter customerId " + customerId);

        CustomerData customer = null;

        Connection connection = null;
        ResultSet rs  = null;
        PreparedStatement ps =null;

        try{
            connection = DBHelper.getConnection();
            ps = connection.prepareStatement(SQL_SELECT_CUSTOMER);
            ps.setInt(1, customerId);
            rs = ps.executeQuery();

            rs.next();
            String address = rs.getString("ADDRESS");
            String inn = rs.getString("INN");
            String label = rs.getString("LABEL");
            Boolean active = new Boolean(rs.getInt("IS_ACTIVE")==1);
            customer = new CustomerDataImpl(customerId,label, inn, address,active);

        } catch(Exception e){
            logger.fatal(e);
            customer = null;
            throw new Exception("Получить данные о грузополучателе не удалось. Ошибка : " + e.getMessage(),e);
        } finally{
            DBHelper.closeConnection(connection);
        }

        logger.debug("#getCustomerById exit");
        return customer;
    }

    public void activateCustomer(Integer customerId, boolean state) throws Exception {
        logger.debug("#activateCustomer enter");
        Connection connection = null;
        PreparedStatement ps = null;

        try{
            connection = DBHelper.getConnection();
            ps = connection.prepareStatement(SQL_ACTIVATE_CUSTOMER);

            int active = 0;
            if(state) {
                active = 1;
            }
            ps.setInt(1, active);
            ps.setInt(2, customerId);
            ps.executeUpdate();
            ps.close();

        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Изменить данные покупателя не удалось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#deleteCustomer exit");
    }

    public List<CustomerData> getActiveCustomers() throws Exception {
        return getCustomers(SQL_SELECT_ACTIVE_CUSTOMERS);
    }

    public List<CustomerData> getAllCustomers() throws Exception {
        return getCustomers(SQL_SELECT_ALL_CUSTOMERS);
    }
}
